--Function of getting current user
create or replace procedure meta_get_current_user(
  a_current_user_name out varchar2)
as
begin
    select user into a_current_user_name from dual;
end;

--go

--Function of checking user existence
create or replace procedure p_check_user_exists(
    --User name
    a_user_name varchar2)
as
    --Row count
    a_row_count number;
begin
    select count(*) into a_row_count
    from meta_users
    where user_name = lower(a_user_name);
    
    if a_row_count = 0 then
         raise_application_error(-20036,
                'A user with the specified name was not found (user_name = '
                || a_user_name
                || ') (#error_code = 20036)');
    end if;
end;

--go

--Function of adding new user
create or replace procedure meta_add_user(
    --User name
    a_user_name varchar2,
    --User password
    a_user_password varchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for a user with the same name
    select count(*) into a_row_count
    from meta_users
    where user_name = lower(a_user_name);
    
    if a_row_count > 0 then
        raise_application_error(-20041,
                'A user with the specified name was already added (user_name = '
                || a_user_name
                || ') (#error_code = 20041)');
    end if;
    
    --Checking if a user with the specified name exists in the database
    select count(*) into a_row_count from all_users where username = upper(a_user_name);

    --If there is no user with the specified name
    if a_row_count = 0 then
        --If the user password was specified
        if a_user_password is not null and length(trim(a_user_password)) > 0 then
            --Creating a new user
            execute immediate 'create user ' || a_user_name
            || ' identified by "' || a_user_password || '"';
        --If the user password was not specified
        else
            --Creating a new user with the default password
            execute immediate 'create user ' || a_user_name
            || ' identified by "P@ssw0rd"';
        end if;        
        
    --If there is a user with the specified name and the new password was specified
    elsif a_user_password is not null and length(trim(a_user_password)) > 0 then
        --Altering the user password
        execute immediate 'alter user ' || a_user_name
        || ' identified by "' || a_user_password || '"';
    end if;
    
    --Granting default privileges
    
    --Read metadata version
    execute immediate 'grant select on meta_version to ' || a_user_name;
    
    --Read and write dbms features
    execute immediate 'grant select on meta_dbms_features to ' || a_user_name;
    execute immediate 'grant update on meta_dbms_features to ' || a_user_name;
    
    --Read schema metadata
    execute immediate 'grant select on meta_tables to ' || a_user_name;
    execute immediate 'grant select on meta_string_columns to ' || a_user_name;
    execute immediate 'grant select on meta_ref_columns to ' || a_user_name;
    execute immediate 'grant select on meta_ref_targets to ' || a_user_name;
    execute immediate 'grant select on meta_enum_columns to ' || a_user_name;
    execute immediate 'grant select on meta_enum_members to ' || a_user_name;
    execute immediate 'grant select on meta_big_int_columns to ' || a_user_name;
    execute immediate 'grant select on meta_binary_columns to ' || a_user_name;
    execute immediate 'grant select on meta_bool_columns to ' || a_user_name;
    execute immediate 'grant select on meta_datetime_columns to ' || a_user_name;
    execute immediate 'grant select on meta_decimal_columns to ' || a_user_name;
    execute immediate 'grant select on meta_guid_columns to ' || a_user_name;
    execute immediate 'grant select on meta_int_columns to ' || a_user_name;
    execute immediate 'grant select on meta_small_int_columns to ' || a_user_name;
    execute immediate 'grant select on meta_text_columns to ' || a_user_name;
    execute immediate 'grant select on meta_table_columns to ' || a_user_name;
    
    --Reading indexes
    execute immediate 'grant select on meta_indexes to ' || a_user_name;
    
    --Reading options
    execute immediate 'grant select on meta_options to ' || a_user_name;
    
    --Executing common functions
    execute immediate 'grant execute on meta_get_dbms_name to ' || a_user_name;
    execute immediate 'grant execute on meta_get_dbms_version to ' || a_user_name;
    execute immediate 'grant execute on meta_get_current_db_name to ' || a_user_name;
    execute immediate 'grant execute on meta_get_current_user to ' || a_user_name;
    execute immediate 'grant execute on meta_set_curr_user_password to ' || a_user_name;
    
    --Reading user names and display names
    execute immediate 'grant select on meta_user_names to ' || a_user_name;
    
    --Current user properties
    execute immediate 'grant select on meta_current_user to ' || a_user_name; 
    execute immediate 'grant update(display_name) on meta_current_user to ' || a_user_name; 
    
    --Read unit list
    execute immediate 'grant select on meta_units to ' || a_user_name;
    execute immediate 'grant select on meta_unit_parents to ' || a_user_name;
    execute immediate 'grant select on meta_user_units to ' || a_user_name;
    
    --Read user's acl unit-level access rights
    execute immediate 'grant select on meta_user_acl_unit_access to ' || a_user_name;
    --Read user's acl full access rights
    execute immediate 'grant select on meta_user_acl_full_access to ' || a_user_name;
    --Read user's acl own rows access rights
    execute immediate 'grant select on meta_user_acl_own_rows to ' || a_user_name;
    
    --Soft delete table rows
    execute immediate 'grant execute on meta_soft_delete_table_row to ' || a_user_name;
    
    --Calling main security function (security check is implemented inside this function)
    execute immediate 'grant execute on meta_set_user_change_security to ' || a_user_name;
    
    --Updating the metadata
    insert into meta_users(user_name)
    values(lower(a_user_name));
    
    --Granting login permission
    meta_set_user_login(a_user_name, 1);
end;

--go

--Function of allowing user login
create or replace procedure meta_set_user_login(
    --User name
    a_user_name varchar2,
    --Can login
    a_can_login number)
as
    --Old CanLogin
    a_old_can_login number(1);
begin
    p_check_user_exists(a_user_name);
    
    --If it is current user
    if lower(a_user_name) = lower(user) then
        raise_application_error(-20042,
                'Changing current user login permission is forbidden (user_name = '
                || a_user_name
                || ') (#error_code = 20042)');
    end if;

    --Reading old CanLogin
    select can_login
    into a_old_can_login
    from meta_users
    where user_name = lower(a_user_name);
    
    --if there is no need for change
    if a_can_login = a_old_can_login then
        return;
    end if;
    
    --Changing the metadata
    update meta_users
    set can_login = a_can_login
    where user_name = lower(a_user_name);
    
    --Granting or revoking the privilege
    if a_can_login = 1 then
        execute immediate 'grant create session to ' || a_user_name;
    else
        execute immediate 'revoke create session from ' || a_user_name;
    end if;

end;

--go

--Function of setting user CanChangeSchema
create or replace procedure meta_set_user_change_schema(
    --User name
    a_user_name varchar2,
    --Can change schema
    a_can_change_schema number)
as
    --Current user can change security
    a_user_can_change_security number(1);
    --Old CanChangeSchema
    a_old_can_change_schema number(1);
begin
    p_check_user_exists(a_user_name);
    
    --Reading old CanChangeSchema
    select can_change_schema
    into a_old_can_change_schema
    from meta_users
    where user_name = lower(a_user_name);
    
    --If there is no need for change
    if a_can_change_schema = a_old_can_change_schema then
        return;
    end if;
    
    if a_can_change_schema = 1 then
        p_enable_schema_change(a_user_name);
    else
        p_disable_schema_change(a_user_name); 
    end if;
    
    --Changing the metadata
    update meta_users
    set can_change_schema = a_can_change_schema
    where user_name = lower(a_user_name);
    
end;

--go

--Function of enabling schema change
create or replace procedure p_enable_schema_change(
    --User name
    a_user_name varchar2)
as
begin
    --Update metadata version
    execute immediate 'grant update on meta_version to ' || a_user_name;
    
    --Granting update on meta tables
    execute immediate 'grant update(display_name, description) on meta_tables to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_string_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_ref_columns to ' || a_user_name;
    execute immediate 'grant update(cascade_delete) on meta_ref_targets to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_enum_columns to ' || a_user_name;
    execute immediate 'grant update(display_name) on meta_enum_members to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_big_int_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_binary_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_bool_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_datetime_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_decimal_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_guid_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_int_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_small_int_columns to ' || a_user_name;
    execute immediate 'grant update(display_name, description, order_index) on meta_text_columns to ' || a_user_name;
    
    --Grant execute on schema changing functions
    execute immediate 'grant execute on meta_add_table to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_table to ' || a_user_name;
    execute immediate 'grant execute on meta_rename_table to ' || a_user_name;
    execute immediate 'grant execute on meta_add_string_column to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_string_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_column_nullability to ' || a_user_name;
    execute immediate 'grant execute on meta_set_string_max_length to ' || a_user_name;
    execute immediate 'grant execute on meta_rename_column to ' || a_user_name;
    execute immediate 'grant execute on meta_add_ref_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_ref_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_ref_target to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_ref_target to ' || a_user_name;
    execute immediate 'grant execute on meta_set_ref_checking to ' || a_user_name;    
    execute immediate 'grant execute on meta_add_enum_column to ' || a_user_name;
    execute immediate 'grant execute on meta_add_enum_member to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_enum_member to ' || a_user_name;
    execute immediate 'grant execute on meta_set_enum_member_value to ' || a_user_name;
    execute immediate 'grant execute on meta_set_enum_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_enum_value_checking to ' || a_user_name;
    execute immediate 'grant execute on meta_add_decimal_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_precision to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_scale to ' || a_user_name;
    execute immediate 'grant execute on meta_set_decimal_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_datetime_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_datetime_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_datetime_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_datetime_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_bool_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_bool_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_binary_column to ' || a_user_name;
    execute immediate 'grant execute on meta_add_int_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_int_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_int_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_int_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_add_big_int_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_big_int_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_big_int_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_big_int_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_add_small_int_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_small_int_default to ' || a_user_name;
    execute immediate 'grant execute on meta_set_small_int_maximum to ' || a_user_name;
    execute immediate 'grant execute on meta_set_small_int_minimum to ' || a_user_name;
    execute immediate 'grant execute on meta_add_guid_column to ' || a_user_name;
    execute immediate 'grant execute on meta_set_guid_default to ' || a_user_name;
    execute immediate 'grant execute on meta_add_text_column to ' || a_user_name;
    execute immediate 'grant execute on meta_add_index to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_index to ' || a_user_name;
    
    --Restoring table rows
    execute immediate 'grant execute on meta_restore_table_row to ' || a_user_name;
    --Hard deleting table rows
    execute immediate 'grant execute on meta_delete_table_row to ' || a_user_name;
    
    --Enabling/disabling recycle bin
    execute immediate 'grant execute on meta_set_recycle_bin to ' || a_user_name;
    
    --Granting all rights to all user tables
    for t in (select table_name from meta_tables) loop
        execute immediate 'grant select, insert, update, delete on tbl_' || t.table_name || ' to ' || a_user_name;
    end loop;
end;

--go

--Function of disabling schema change
create or replace procedure p_disable_schema_change(
    --User name
    a_user_name varchar2)
as
begin
    --Revoking rights

    --Update metadata version
    execute immediate 'revoke update on meta_version from ' || a_user_name;
    
    --Revoking update on meta tables
    execute immediate 'revoke update on meta_tables from ' || a_user_name;
    execute immediate 'revoke update on meta_string_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_ref_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_ref_targets from ' || a_user_name;
    execute immediate 'revoke update on meta_enum_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_enum_members from ' || a_user_name;
    execute immediate 'revoke update on meta_big_int_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_binary_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_bool_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_datetime_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_decimal_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_guid_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_int_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_small_int_columns from ' || a_user_name;
    execute immediate 'revoke update on meta_text_columns from ' || a_user_name;
    
    --Grant execute on schema changing functions
    execute immediate 'revoke execute on meta_add_table from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_table from ' || a_user_name;
    execute immediate 'revoke execute on meta_rename_table from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_string_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_string_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_column_nullability from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_string_max_length from ' || a_user_name;
    execute immediate 'revoke execute on meta_rename_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_ref_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_ref_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_ref_target from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_ref_target from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_ref_checking from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_enum_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_enum_member from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_enum_member from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_enum_member_value from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_enum_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_enum_value_checking from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_decimal_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_precision from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_scale from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_decimal_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_datetime_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_datetime_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_datetime_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_datetime_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_bool_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_bool_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_binary_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_int_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_int_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_int_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_int_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_big_int_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_big_int_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_big_int_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_big_int_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_small_int_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_small_int_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_small_int_maximum from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_small_int_minimum from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_guid_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_guid_default from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_text_column from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_index from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_index from ' || a_user_name;
    
    --Restoring table rows
    execute immediate 'revoke execute on meta_restore_table_row from ' || a_user_name;
    --Hard deleting table rows
    execute immediate 'revoke execute on meta_delete_table_row from ' || a_user_name;
    
    --Enabling/disabling recycle bin
    execute immediate 'revoke execute on meta_set_recycle_bin from ' || a_user_name;
    
    --Revoking all rights from all user tables
    for t in (select table_name from meta_tables) loop
        execute immediate 'revoke select, insert, update, delete on tbl_' || t.table_name ||  ' from ' || a_user_name;
    end loop;
end;

--go

--Function of setting user's ability to change security
create or replace procedure meta_set_user_change_security(
    --User name
    a_user_name varchar2,
    --Can change security
    a_can_change_security number)
as
    --Old CanChangeSecurity
    a_old_can_change_security number(1);
    --Current user CanChangeSecurity
    a_curr_user_change_security number(1);
    --Row count
    a_row_count number;
begin
    p_check_user_exists(a_user_name);
    
    --If it is current user
    if lower(a_user_name) = lower(user) then
        raise_application_error(-20043,
                'Changing current user security change permission is forbidden (user_name = '
                || a_user_name
                || ') (#error_code = 20043)');
    end if;
    
    --Searching for current user
    select count(*) into a_row_count
    from meta_users
    where user_name = lower(user);
    
    --If current user was found
    if a_row_count <> 0 then
        --Reading current user ability to change security
        select can_change_security
        into a_curr_user_change_security
        from meta_users
        where user_name = lower(user);
        
        if a_curr_user_change_security = 0 then
            raise_application_error(-20045,
                'Insufficient privileges to change another user security permissions (user_name = '
                || a_user_name
                || ') (#error_code = 20045)');
        end if;
    end if;

    --Reading old CanChangeSecurity
    select can_change_security
    into a_old_can_change_security
    from meta_users
    where user_name = lower(a_user_name);
    
    --If there is no need for change
    if a_can_change_security = a_old_can_change_security then
        return;
    end if;
    
    if a_can_change_security = 1 then
        p_enable_security_change(a_user_name);
    else
        p_disable_security_change(a_user_name);
    end if;
    
     --Changing the metadata
    update meta_users
    set can_change_security = a_can_change_security
    where user_name = lower(a_user_name);
end;

--go

--Function of enabling security change
create or replace procedure p_enable_security_change(
    --User name
    a_user_name varchar2)
as
begin
    --Granting read on security metadata
    execute immediate 'grant select on meta_users to ' || a_user_name;
    execute immediate 'grant select on meta_user_roles to ' || a_user_name;
    execute immediate 'grant select on meta_role_roles to ' || a_user_name;
    execute immediate 'grant select on meta_roles to ' || a_user_name;
    execute immediate 'grant select on meta_principals to ' || a_user_name;
    execute immediate 'grant select on meta_table_acl to ' || a_user_name;
    execute immediate 'grant select on meta_table_acl_unit_access to ' || a_user_name;
    execute immediate 'grant select on meta_table_acl_full_access to ' || a_user_name;
    execute immediate 'grant select on meta_table_acl_own_rows to ' || a_user_name;
    
    --Granting update on security metadata
    execute immediate 'grant update(display_name) on meta_users to ' || a_user_name;
    execute immediate 'grant update(unit_name, display_name) on meta_units to ' || a_user_name;
    execute immediate 'grant update(display_name) on meta_roles to ' || a_user_name;
    
    --Granting execute on security admin functions
    execute immediate 'grant execute on meta_add_unit to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_unit to ' || a_user_name;
    execute immediate 'grant execute on meta_add_unit_parent to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_unit_parent to ' || a_user_name;
    execute immediate 'grant execute on meta_add_role to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_role to ' || a_user_name;
    execute immediate 'grant execute on meta_add_user to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_user to ' || a_user_name;
    execute immediate 'grant execute on meta_set_user_login to ' || a_user_name;
    execute immediate 'grant execute on meta_set_user_change_schema to ' || a_user_name;
    execute immediate 'grant execute on meta_add_user_unit to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_user_unit to ' || a_user_name;
    execute immediate 'grant execute on meta_add_user_in_role to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_user_from_role to ' || a_user_name;
    execute immediate 'grant execute on meta_add_role_in_role to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_role_from_role to ' || a_user_name;
    execute immediate 'grant execute on meta_add_table_rights to ' || a_user_name;
    execute immediate 'grant execute on meta_delete_table_rights to ' || a_user_name;
    execute immediate 'grant execute on meta_set_user_password to ' || a_user_name;
    execute immediate 'grant execute on meta_set_unit_security to ' || a_user_name;   

end;

--go

--Function of disabling user ability to change security
create or replace procedure p_disable_security_change(
    --User name
    a_user_name varchar2)
as
begin
    --Revoking read on security metadata
    execute immediate 'revoke select on meta_users from ' || a_user_name;
    execute immediate 'revoke select on meta_user_roles from ' || a_user_name;
    execute immediate 'revoke select on meta_role_roles from ' || a_user_name;
    execute immediate 'revoke select on meta_roles from ' || a_user_name;
    execute immediate 'revoke select on meta_principals from ' || a_user_name;
    execute immediate 'revoke select on meta_table_acl from ' || a_user_name;
    execute immediate 'revoke select on meta_table_acl_unit_access from ' || a_user_name;
    execute immediate 'revoke select on meta_table_acl_full_access from ' || a_user_name;
    execute immediate 'revoke select on meta_table_acl_own_rows from ' || a_user_name;
    
    --Revoking update on security metadata
    execute immediate 'revoke update on meta_users from ' || a_user_name;
    execute immediate 'revoke update on meta_units from ' || a_user_name;
    execute immediate 'revoke update on meta_roles from ' || a_user_name;
    
    --Revoking execute on security admin functions
    execute immediate 'revoke execute on meta_add_unit from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_unit from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_unit_parent from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_unit_parent from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_user from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_user from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_user_login from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_user_change_schema from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_user_unit from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_user_unit from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_user_in_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_user_from_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_role_in_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_role_from_role from ' || a_user_name;
    execute immediate 'revoke execute on meta_add_table_rights from ' || a_user_name;
    execute immediate 'revoke execute on meta_delete_table_rights from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_user_password from ' || a_user_name;
    execute immediate 'revoke execute on meta_set_unit_security from ' || a_user_name; 
    
    --!!! No need to revoke execute from meta_set_user_change_security
    -- Otherwise there might be a deadlock
end;

--go

--Function of deleting a user
create or replace procedure meta_delete_user(
    --User name
    a_user_name varchar2)
as
    --Current index
    a_curr_index number;
    --Affected users
    a_affected_users meta_name_list := meta_name_list();
begin
    p_check_user_exists(a_user_name);
    
    --If it is current user
    if lower(a_user_name) = lower(user) then
        raise_application_error(-20046,
                'Deleting current user is forbidden (user_name = '
                || a_user_name
                || ') (#error_code = 20046)');
    end if;
    
    --Updating the metadata
    delete from meta_users
    where user_name = lower(a_user_name);
    
    --Deleting user acl
    delete from meta_table_acl
    where principal_name = lower(a_user_name);
end;

--go

--Function of getting user principals
create or replace function p_get_user_principals(
    --User name
    a_user_name varchar2)
    return meta_name_list
as
    --Name list
    a_name_list meta_name_list := meta_name_list();
    --Roles to search
    a_role_to_search_list meta_name_list := meta_name_list();
begin
    with user_roles as (
        select container_role_name
        from meta_user_roles
        where user_name = lower(a_user_name))
    select distinct principal
    bulk collect into a_name_list
    from (
        --The user name
        select a_user_name as principal from dual
        union        
        --The user roles
        select container_role_name as principal from user_roles
        union
        --That roles parents
        select container_role_name as principal
        from meta_role_roles
        start with role_name in (select container_role_name from user_roles)
        connect by prior container_role_name = role_name);
    
    return a_name_list;
end;

--go

--Function of adding user's unit
create or replace procedure meta_add_user_unit(
    --User name
    a_user_name varchar2,
    --Unit id
    a_unit_id raw)
as
    --Row count
    a_row_count number;
    --Affected new users
    a_affected_new_users meta_name_list := meta_name_list();
begin
    p_check_user_exists(a_user_name);
    p_check_unit_exists(a_unit_id);

    --Searching for the same user unit
    select count(*) into a_row_count
    from meta_user_units
    where user_name = lower(a_user_name)
    and unit_id = a_unit_id;
    
    if a_row_count > 0 then
        raise_application_error(-20052,
                'Specified user unit was already added (unit_id = '
                || a_unit_id || ', user_name = '
                || a_user_name
                || ') (#error_code = 20052)');
    end if;
    
    --Updating the metadata
    insert into meta_user_units (user_name, unit_id)
    values (a_user_name, a_unit_id);
    
    --Refreshing the user acl
    p_refresh_user_acl(a_user_name);
end;

--go

--Function of deleting user's unit
create or replace procedure meta_delete_user_unit(
    --User name
    a_user_name varchar2,
    --Unit id
    a_unit_id raw)
as
    --Row count
    a_row_count number;
    --Affected old users
    a_affected_old_users meta_name_list := meta_name_list();
begin
    p_check_user_exists(a_user_name);
    p_check_unit_exists(a_unit_id);

    --Searching for the user unit
    select count(*) into a_row_count
    from meta_user_units
    where user_name = lower(a_user_name)
    and unit_id = a_unit_id;
    
    if a_row_count = 0 then
        raise_application_error(-20053,
                'Specified user unit was not found (unit_id = '
                || a_unit_id || ', user_name = '
                || a_user_name
                || ') (#error_code = 20053)');
    end if;
    
    --Updating the metadata
    delete from meta_user_units
    where user_name = lower(a_user_name)
    and unit_id = a_unit_id;
    
    --Refreshing the user acl
    p_refresh_user_acl(a_user_name);
end;

--go

--Function of changing user password
create or replace procedure meta_set_user_password(
    --User name
    a_user_name varchar2,
    --New password
    a_new_password varchar2)
as
    --Row count
    a_row_count number;
begin
    p_check_user_exists(a_user_name);

    execute immediate 'alter user ' || a_user_name
    || ' identified by "' || a_new_password || '"';
end;

--go

--Function of changing current user password
create or replace procedure meta_set_curr_user_password(
    --Old password
    a_old_password varchar2,
    --New password
    a_new_password varchar2)
as
begin
    p_check_user_exists(user);

    execute immediate 'alter user ' || user
    || ' identified by "' || a_new_password || '" replace "' || a_old_password || '"';
end;

--go

--Function of enabling/disabling table unit level security
create or replace procedure meta_set_unit_security(
    --Table id
    a_table_id raw,
    --Unit level security
    a_unit_level_security numeric)
as
    --Table name
    a_table_name varchar2(26);
begin
     p_check_table_exists(a_table_id);
     
     a_table_name := p_get_table_name(a_table_id);
     
     --Recreating table view
     p_drop_view_if_exists(a_table_name);
     
     --Updating the metadata
     update meta_tables
     set unit_level_security = a_unit_level_security
     where table_id = a_table_id;
     
     p_create_table_view(a_table_id);
end;

--go